#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Date;
use Config::IniFiles;
use Time::Local;
use XML::LibXML;

require "/var/www/cgi-bin/gnugraph/rcoos/timeZone.lib";
my $graph_config="/var/myscheduler/graph.xml";
my $xp_graph = XML::LibXML->new->parse_file($graph_config);

my $station=$ARGV[0];
my $platform_handle=$ARGV[1];
my $startd=$ARGV[2];
my $endd=$ARGV[3];
my $format=$ARGV[4];
my $dtype=$ARGV[5];
my $timezone=$ARGV[6];
my $output=$ARGV[7];

if($endd){$endd="$endd 23:59:59";}

my @array1=split(/-/,$dtype);
my $datatype=$array1[0];
my $depth_string;
if($array1[1]){
    $depth_string=substr($array1[1],0,-1);
    $depth_string=" and sensor.fixed_z=$depth_string";
}

my @array1=split(/-/,$format);
my $sort_string=$array1[0]." ".$array1[1];

#my $dbname="/var/www/html/RCOOS/website/latest.db";

#my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
                  #  { RaiseError => 1, AutoCommit => 1 });
#if(!defined $dbh) {die "Cannot connect to database!\n";}

my $dbh=getdbConn();
if($dbh==0){ die "Cannot connect to database!\n";}

my ($sensor_id,$mtype_id,$standardname,$definition,$uom,$formula);

my $sql="select sensor.row_id,m_type.row_id,obs_type.standard_name,obs_type.definition,uom_type.standard_name,obs_type.multiple from sensor 
         left join m_type on m_type.row_id=sensor.m_type_id
         left join m_scalar_type on m_scalar_type.row_id=m_type.m_scalar_type_id
         left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
         left join uom_type on uom_type.row_id=m_scalar_type.uom_type_id
         left join platform on sensor.platform_id=platform.row_id
         where sensor.short_name='$datatype' $depth_string and platform.platform_handle = '$platform_handle'";

 my $sth = $dbh->prepare($sql);
 $sth->execute();
 my $col="m_value";
 while (my @row = $sth->fetchrow_array) {
      $sensor_id=$row[0];
      $mtype_id=$row[1];
      $standardname=$row[2];
      $definition=$row[3];
      ($uom,$formula)=uom_convert($row[2],$row[4]);
      
      if($row[5]==1){$col="m_value_list";}
 }

  
 my $sql="select m_date,m_lon,m_lat,m_z,$col,qc_level from multi_obs where m_date>='$startd' and m_date<='$endd' and platform_handle='$platform_handle' and sensor_id=$sensor_id and m_type_id=$mtype_id order by $sort_string";
 $sth = $dbh->prepare($sql);
 $sth->execute();
 
 my $i=0;

 if($output eq "tab"){
    print "<pre>";
    print "$station $dtype Query Results:\n";
    print "Time Period: $startd ~ $endd\n";
    print "Standard Name: $definition \n Unit: $uom \n"; 
 }
 else {
    print "$station $dtype Query Results:<br>";
    print "Time Period: $startd ~ $endd<br>";
    print "Standard Name: $definition<br>Unit: $uom <br>"; 
 }
 while (my @row = $sth->fetchrow_array) {
     $row[4]=value_convert($formula,$row[4]);
     if($i==0){
        if($output eq "tab"){
           printf "Measurement Date($timezone) \t Latitude \t Logitude \t Depth \t Value \t QC_Level\n";
        }
        else {
           printf "<table width=600 border=1><tr><td>Measurement Date($timezone)</td><td> Latitude </td><td> Logitude </td><td> Depth </td><td> Value </td><td> QC_Level</td></tr>";
        }
     }

     $row[0]=timeConvert($row[0],$timezone);
     $row[1]=sprintf("%.2f",$row[1]);
     $row[2]=sprintf("%.2f",$row[2]);
     if($output eq "tab"){
        print "$row[0] \t $row[1] \t $row[2] \t         $row[3] \t $row[4] \t $row[5]\n";; 
     }
     else {
        print "<tr><td>$row[0] &nbsp; </td><td> $row[1] &nbsp;</td><td> $row[2] &nbsp;</td><td> $row[3]&nbsp; </td><td> $row[4] &nbsp;</td><td> $row[5]&nbsp; </td></tr>";; 
     }
     $i++;
 }

 if($i==0){
    if($output eq "tab"){
      print "No Data Found\n";
    }
    else {
      print "No Data Found<br>";
    }
 }
 else {
     if($output eq "html"){
       print "</table>";
     }
 }

 if($output eq "tab"){   
    print "</pre>";
 }

 $sth->finish();
 undef $sth;
 $dbh->disconnect();

 sub uom_convert {

 my ($obs_type,$uom) = @_;

 my $unit_conversion = '';

 ##CONFIG_START
 #conversions for the following which were labeled wrong on the way in to db
 if ($obs_type eq 'dissolved_oxygen') { $obs_type = 'oxygen_concentration'; }
 if ($uom eq 'millibar') { $uom = 'mb'; }
 if ($uom eq 'percent_saturation') { $uom = 'percent'; }
 ##CONFIG_END

  my $unit_conversion = $xp_graph->findvalue('//observation_list/observation[@standard_name="'.$obs_type.'.'.$uom.'"]/standard_uom_en');
  if ($unit_conversion eq '') { print "not found: $obs_type $uom \n"; return ($uom,$unit_conversion); }

  my $y_title="";
  my $conversion_formula="";
  $y_title = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/y_title');
  if($y_title eq ""){$y_title=$uom;}
  $conversion_formula = $xp_graph->findvalue('//unit_conversion_list/unit_conversion[@id="'.$uom.'_to_'.$unit_conversion.'"]/conversion_formula');

 #unit conversion using supplied equation(e.g. celcius to fahrenheit)
 #my $conversion_string = $conversion_formula;
  #  $conversion_string =~ s/var1/$measurement/g;
 #my $conversion_val = eval $conversion_string;

 return ($y_title,$conversion_formula);

}

sub value_convert {

 my ($conversion_string,$measurement) = @_;
    $conversion_string =~ s/var1/$measurement/g;
 my $conversion_val = eval $conversion_string;
 return $conversion_val; 
}

